Count Many Records

--Apple-Mail-6--728830394
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed

Thanks in advance for your help, and forgive me if this is me being
bone-headed. :-)

The following code works and accomplishes what I need it to do, but I
am wondering if there is a better way to accomplish the same task. I
have several tables of records and I need to count and then display.
Even if I could just combine the first three queries (below) into one
that would probably be a lot better than what I am doing now.

$data1_query = "select id from data1 WHERE status = ''";
$data1_results = mysql_query($data1_query);
$data1_count = mysql_num_rows($data1_results);

$data1p_query = "select id from data1 WHERE status = 'p'";
$data1p_results = mysql_query($data1p_query);
$data1p_count = mysql_num_rows($data1p_results);

$data1h_query = "select id from data1 WHERE status = 'h'";
$data1h_results = mysql_query($data1h_query);
$data1h_count = mysql_num_rows($data1h_results);

echo "";
echo "$data1_count";
echo "$data1h_count";
echo "$data1p_count";
echo "";

Then do the same thing for the second table, and so on.

$data2_query = "select id from data2 WHERE status = ''";
$data2_results = mysql_query($data2_query);
$data2_count = mysql_num_rows($data2_results);

$data2p_query = "select id from data2 WHERE status = 'p'";
$data2p_results = mysql_query($data2p_query);
$data2p_count = mysql_num_rows($data2p_results);

$data2h_query = "select id from data2 WHERE status = 'h'";
$data2h_results = mysql_query($data2h_query);
$data2h_count = mysql_num_rows($data2h_results);

echo "";
echo "$data2_count";
echo "$data2h_count";
echo "$data2p_count";
echo "";

--
Kevin Murphy
Webmaster: Information and Marketing Services
Western Nevada Community College
www.wncc.edu
775-445-3326



--
Kevin Murphy
Webmaster: Information and Marketing Services
Western Nevada Community College
www.wncc.edu
775-445-3326



--Apple-Mail-6--728830394--

Re: Count Many Records

> Kevin Murphy asks:
> The following code works and accomplishes what I need it to do, but I
> am wondering if there is a better way to accomplish the same task. I
> have several tables of records and I need to count and then display.
> Even if I could just combine the first three queries (below) into one
> that would probably be a lot better than what I am doing now.
>
> $data1_query = "select id from data1 WHERE status = ''";
> $data1_results = mysql_query($data1_query);
> $data1_count = mysql_num_rows($data1_results);
>
> $data1p_query = "select id from data1 WHERE status = 'p'";
> $data1p_results = mysql_query($data1p_query);
> $data1p_count = mysql_num_rows($data1p_results);
>
> $data1h_query = "select id from data1 WHERE status = 'h'";
> $data1h_results = mysql_query($data1h_query);
> $data1h_count = mysql_num_rows($data1h_results);
>
> echo "";
> echo "$data1_count";
> echo "$data1h_count";
> echo "$data1p_count";
> echo "";
>
> Then do the same thing for the second table, and so on.
>
> $data2_query = "select id from data2 WHERE status = ''";
> $data2_results = mysql_query($data2_query);
> $data2_count = mysql_num_rows($data2_results);
>
> $data2p_query = "select id from data2 WHERE status = 'p'";
> $data2p_results = mysql_query($data2p_query);
> $data2p_count = mysql_num_rows($data2p_results);
>
> $data2h_query = "select id from data2 WHERE status = 'h'";
> $data2h_results = mysql_query($data2h_query);
> $data2h_count = mysql_num_rows($data2h_results);
>
> echo "";
> echo "$data2_count";
> echo "$data2h_count";
> echo "$data2p_count";
> echo "";

Kevin,

You haven't said what you want the page to look like, but you could have a
html select box of "letters" (a,b,c,d...) that would allow for the
selection of multiple items, then grab the values out of the array created
and add them to the select query, and loop through it:
$data1_query = "select id from data1 WHERE status
= '$value_from_letter_array'";
Then process as you have done.

Or you could use a checkbox array...

Does that help any?

David

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Count Many Records

--Apple-Mail-4--724586540
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed

Actually the design is in the code below.... I need to display the
counts from all of those queries in a grid. Basically, its just a
summary of a bunch of information. Another way to look at it would be
a several lines that say something like this:

You have $data1_count NEW records in data1,
You have $data1p_count PROCESSED records in data1,
etc.

--
Kevin Murphy
Webmaster: Information and Marketing Services
Western Nevada Community College
www.wncc.edu
775-445-3326


On Jun 28, 2006, at 1:56 PM, dpgirago@mdanderson.org wrote:

>
>> Kevin Murphy asks:
>> The following code works and accomplishes what I need it to do, but I
>> am wondering if there is a better way to accomplish the same task. I
>> have several tables of records and I need to count and then display.
>> Even if I could just combine the first three queries (below) into one
>> that would probably be a lot better than what I am doing now.
>>
>> $data1_query = "select id from data1 WHERE status = ''";
>> $data1_results = mysql_query($data1_query);
>> $data1_count = mysql_num_rows($data1_results);
>>
>> $data1p_query = "select id from data1 WHERE status = 'p'";
>> $data1p_results = mysql_query($data1p_query);
>> $data1p_count = mysql_num_rows($data1p_results);
>>
>> $data1h_query = "select id from data1 WHERE status = 'h'";
>> $data1h_results = mysql_query($data1h_query);
>> $data1h_count = mysql_num_rows($data1h_results);
>>
>> echo "";
>> echo "$data1_count >> td>";
>> echo "
>> $data1h_count";
>> echo "
>> $data1p_count";
>> echo "";
>>
>> Then do the same thing for the second table, and so on.
>>
>> $data2_query = "select id from data2 WHERE status = ''";
>> $data2_results = mysql_query($data2_query);
>> $data2_count = mysql_num_rows($data2_results);
>>
>> $data2p_query = "select id from data2 WHERE status = 'p'";
>> $data2p_results = mysql_query($data2p_query);
>> $data2p_count = mysql_num_rows($data2p_results);
>>
>> $data2h_query = "select id from data2 WHERE status = 'h'";
>> $data2h_results = mysql_query($data2h_query);
>> $data2h_count = mysql_num_rows($data2h_results);
>>
>> echo "";
>> echo "$data2_count >> td>";
>> echo "
>> $data2h_count";
>> echo "
>> $data2p_count";
>> echo "";
>
> Kevin,
>
> You haven't said what you want the page to look like, but you could
> have a
> html select box of "letters" (a,b,c,d...) that would allow for the
> selection of multiple items, then grab the values out of the array
> created
> and add them to the select query, and loop through it:
> $data1_query = "select id from data1 WHERE status
> = '$value_from_letter_array'";
> Then process as you have done.
>
> Or you could use a checkbox array...
>
> Does that help any?
>
> David
>
>


--Apple-Mail-4--724586540--

Re: Count Many Records

Kevin Murphy wrote:
> Actually the design is in the code below.... I need to display the
> counts from all of those queries in a grid. Basically, its just a
> summary of a bunch of information. Another way to look at it would be a
> several lines that say something like this:
>
> You have $data1_count NEW records in data1,
> You have $data1p_count PROCESSED records in data1,
> etc.

Instead of

$data1_query = "select id from data1 WHERE status = ''";
$data1_results = mysql_query($data1_query);
$data1_count = mysql_num_rows($data1_results);

$data1p_query = "select id from data1 WHERE status = 'p'";
$data1p_results = mysql_query($data1p_query);
$data1p_count = mysql_num_rows($data1p_results);

$data1h_query = "select id from data1 WHERE status = 'h'";
$data1h_results = mysql_query($data1h_query);
$data1h_count = mysql_num_rows($data1h_results);


You could do it all in one query:

$query = "select status, count(id) AS count from data1 where status in
('', 'p', 'h') group by status";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)) {
echo "status: " . $row['status'] . "
";
echo "count: " . $row['count'] . "
";
}

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Count Many Records

--Apple-Mail-26--638916699
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed

Thanks for your help, Chris. You certainly got me going in the right
direction. In case anyone is interested, below is the completed
solution, with design elements, turned into a function so I can
easily call it several times.

function counter_maker ($database,$name,$link)
{
echo "";
echo " \"content\">$name";

$query = "select status, count(id) AS count from $database where
status in ('', 'h', 'p') group by status";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result))
{
$status_code = $row['status'];
if ($status_code == "")
{ $status_code = "n"; }
$count_array[$status_code] = $row['count'];
}
echo " if (isset($count_array['n']))
{ echo " style=\"background-color: #F3CF45; color: #00599E; font-
weight: bold;\">";
echo $count_array['n']; }
else
{ echo "> 0"; }
echo "";
echo " if (isset($count_array['h']))
{ echo " style=\"background-color: #00599E; color: #F3CF45; font-
weight: bold;\">";
echo $count_array['h']; }
else
{ echo "> 0"; }
echo "";
echo "";
if (isset($count_array['p']))
{ echo $count_array['p']; }
else
{ echo "0"; }
echo "";
}


counter_maker ("data1","Form Name","link.php");



--
Kevin Murphy
Webmaster: Information and Marketing Services
Western Nevada Community College
www.wncc.edu
775-445-3326


On Jun 28, 2006, at 5:39 PM, Chris wrote:

> Kevin Murphy wrote:
>> Actually the design is in the code below.... I need to display the
>> counts from all of those queries in a grid. Basically, its just a
>> summary of a bunch of information. Another way to look at it would
>> be a several lines that say something like this:
>> You have $data1_count NEW records in data1,
>> You have $data1p_count PROCESSED records in data1,
>> etc.
>
> Instead of
>
> $data1_query = "select id from data1 WHERE status = ''";
> $data1_results = mysql_query($data1_query);
> $data1_count = mysql_num_rows($data1_results);
>
> $data1p_query = "select id from data1 WHERE status = 'p'";
> $data1p_results = mysql_query($data1p_query);
> $data1p_count = mysql_num_rows($data1p_results);
>
> $data1h_query = "select id from data1 WHERE status = 'h'";
> $data1h_results = mysql_query($data1h_query);
> $data1h_count = mysql_num_rows($data1h_results);
>
>
> You could do it all in one query:
>
> $query = "select status, count(id) AS count from data1 where status
> in ('', 'p', 'h') group by status";
> $result = mysql_query($query);
> while($row = mysql_fetch_assoc($result)) {
> echo "status: " . $row['status'] . "
";
> echo "count: " . $row['count'] . "
";
> }
>
> --
> Postgresql & php tutorials
> http://www.designmagick.com/


--Apple-Mail-26--638916699--